﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevComponents.AdvTree;
using DevComponents.DotNetBar;
using Excel;
using System.Threading;
using System.IO;
using ZedGraph;

namespace project_assess.read_excel
{
    public partial class read_Excel_frm : Office2007Form
    {
        Mfrm mfrm;
        project prj;
        DataBase m_db;
        public read_Excel_frm(Mfrm frm)
        {
            
            CheckForIllegalCrossThreadCalls = false;
            InitializeComponent();
            mfrm = frm;
            m_db = frm.m_db;
            load_data();
        }

        delegate void read_btn(object filename);

        /// <summary>
        /// 读入excel,包含项目的基本信息以及工作量
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_read_Click(object sender, EventArgs e)
        {
            OpenFileDialog f = new OpenFileDialog();
            f.Filter = "Excel 2003 文件(*.xls)|*.xls";
            if (f.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                //try
                {
                    //read_btn thr = new read_btn(read_excel);
                    //thr.BeginInvoke(f.FileName, new AsyncCallback(CallBack), thr);
                    //Thread thr = new Thread(new ParameterizedThreadStart(read_excel));
                    //thr.Start(f.FileName);
                    read_excel(f.FileName,checkBox.Checked);
                }
                //catch(Exception a)
                //{
                //    message_frm frm = new message_frm("碰到异常,读入无法进行.\r\n"+a.Message, "异常", icon_img.alert1);
                //    frm.ShowDialog(this);
                //    frm.Close();
                //}
            }
        }
        private void CallBack(IAsyncResult ar)
        {
            //从异步状态ar.AsyncState中，获取委托对象
            read_btn dn = (read_btn)ar.AsyncState;
            //一定要EndInvoke，否则...
            dn.EndInvoke(ar);
        }

        //读入excel
        public void read_excel(object path,bool check)
        {
            object miss=Type.Missing;
            ApplicationClass excel = new ApplicationClass();
            prj = new project();
            Workbook book = excel.Workbooks.Open(path.ToString(), miss, miss, miss, miss, 
                miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            Worksheet sheet = book.Sheets[1] as Worksheet;
            read_excel(sheet,check);
            for (int i = 2; i <= book.Sheets.Count; i++)
            {
                sheet = book.Sheets[i] as Worksheet;
                mfrm.Activate();
                mfrm.Show();
                read_Excel_frm frm = mfrm.read_excel();
                frm.read_excel(sheet, check);
            }
            book.Close(false, miss, miss);
        }
        public void read_excel(Worksheet sheet,bool check)
        {
            object miss = Type.Missing;
            Range r;
            prj = new project();
            r = sheet.Cells[3, 3] as Range;
            prj = new project();
            try
            {
                prj.date = Convert.ToDateTime(r.Text);//评测时间
            }
            catch
            {
                MessageBox.Show("评测时间出错,\r\n已用当前时间代替");
                prj.date = DateTime.Now;
            }
            try
            {
                r = sheet.Cells.Find("工程名称", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.prjname = r.get_Offset(0, 1).Value2.ToString().Trim();//工程名称


                r = sheet.Cells.Find("工程编号", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.prjid = r.get_Offset(0, 1).Value2.ToString();//工程编号
                prj.prjid = prj.prjid.Replace("-", "-");
                prj.prjid = prj.prjid.Trim();
                this.Text = "项目测算:" + prj.prjid;

                r = sheet.Cells.Find("合同额", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.contract = (double)r.get_Offset(0, 1).Value2;//合同额

                r = sheet.Cells.Find("地址", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.address = r.get_Offset(0, 1).Value2.ToString().Trim();//地址

                r = sheet.Cells.Find("项目经理", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.manager = r.get_Offset(0, 1).Value2.ToString().Trim();//项目经理


                r = sheet.Cells.Find("班组长", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.leader = r.get_Offset(0, 1).Value2.ToString().Trim();//班组长
                r = sheet.Cells.Find("采购厂商", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);
                prj.manufacture = r.get_Offset(0, 1).Value2.ToString().Trim();//采购厂商
                r = sheet.Cells.Find("监测类型", miss, miss, miss, miss,
                    XlSearchDirection.xlNext, miss, miss, miss);

                prj.type = r.get_Offset(0, 1).Value2.ToString().Trim();//监测类型
                r = sheet.Cells.Find("次数", miss, miss, miss, miss,
                     XlSearchDirection.xlNext, miss, miss, miss);
            }
            catch
            {
                MessageBox.Show("基本信息填写有误!");
            }
            int jj = r.Row;
            try
            {
                r = sheet.Cells.Find("围护体压顶梁", miss, miss, miss, miss,
                    XlSearchDirection.xlPrevious, miss, miss, miss);
                prj.survey_no = (double)r.get_Offset(0, 4).Value2;//次数
            }
            catch
            {
                prj.survey_no = 0;
            }
            r = sheet.Cells.Find("基坑工作量系数", miss, miss, miss, miss,
                             XlSearchDirection.xlNext, miss, miss, miss);
            r = r.get_Offset(0, 4);
            prj.work_ratio = (double)r.Value2;//工作量系数

            r = sheet.Cells.Find("支撑层数", miss, miss, miss, miss,
                            XlSearchDirection.xlNext, miss, miss, miss);
            r = r.get_Offset(1, 0);
            try
            {
                prj.support_no = (double)r.Value2;//支撑数
            }
            catch
            {
                prj.support_no = 0;
            }
            try
            {
                prj.envi_complicate = (double)r.get_Offset(0, 1).Value2;//环境复杂度
                prj.operate_method = (double)r.get_Offset(0, 2).Value2;//施作方式
            }
            catch
            {
                prj.envi_complicate = 1; prj.operate_method = 0;
            }

            r = sheet.Cells.Find("工程综合系数", miss, miss, miss, miss,
                 XlSearchDirection.xlNext, miss, miss, miss);
            prj.prj_ratio = (double)r.get_Offset(0, 1).Value2;//工程综合系数

            r = sheet.Cells.Find("地域系数", miss, miss, miss, miss,
                XlSearchDirection.xlNext, miss, miss, miss);
            prj.region_ratio = (double)r.get_Offset(1, 0).Value2;//地域系数
            prj.complicate_ratio = (double)r.get_Offset(1, 1).Value2;//复杂系数
            prj.survey_type_ratio = (double)r.get_Offset(1, 2).Value2;//监测类型系数
            prj.team_ratio = (double)r.get_Offset(1, 2).Value2;//班组折减率
            prj.scale_ratio = (double)r.get_Offset(1, 2).Value2;//规模效应折减

            r = sheet.Cells.Find("材料采购系数", miss, miss, miss, miss,
                XlSearchDirection.xlNext, miss, miss, miss);
            prj.manu_ratio = (double)r.get_Offset(0, 1).Value2;//材料采购系数

            r = sheet.Cells.Find("普通测钉", miss, miss, miss, miss,
                XlSearchDirection.xlNext, miss, miss, miss);
            r = r.get_Offset(0, 2);
            string cont = "1";
            item_content content = new item_content();
            string sql = "select max([日期]) from [material_para] where [日期]<'" + prj.date.ToShortDateString()
                + "'";
            string latest1 = mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][0].ToString();

            sql = "select max([日期]) from [bury_para] where [日期]<'" + prj.date.ToShortDateString()
                + "'";
            string latest2 = mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][0].ToString();

            sql = "select max([日期]) from [workload_para] where [日期]<'" + prj.date.ToShortDateString()
              + "'";
            string latest3 = mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][0].ToString();
            //****************材料埋设费用
            while (r.get_Offset(0,2).Value2 != null && 
                r.get_Offset(0,2).Value2.ToString() != string.Empty)
            {
                material_item item = new material_item();
                try
                {
                    item.method = r.get_Offset(0, -2).Value2.ToString();
                }
                catch
                {
                    item.method = "";
                }
                item.number = Convert.ToInt32(r.get_Offset(0, -1).Value2);
                string c = r.get_Offset(0, -3).Text.ToString();
                if (!c.Equals(cont) && c != "")
                {
                    if (cont != "1")
                    {
                        prj.contents.Add(content);
                    }
                    content = new item_content();
                    content.name = c;
                    cont = c;
                }
                if (item.number == 0)
                {
                    if (r.get_Offset(0, 5).Value2 != null && content.parameters == 0)
                    {
                        content.parameters = (double)(r.get_Offset(0, 5).Value2);
                    }
                    r = r.get_Offset(1, 0);
                    continue;
                }
                if (check == false)
                {
                    item.material_price = get_material_price(content.name, item.method, latest1);
                    item.material_total = item.material_price * item.number;
                    item.bury_price = get_bury_price(content.name, item.method, latest2);
                    item.bury_total = item.bury_price * item.number;
                }
                else
                {
                    item.material_price = Convert.ToDouble(r.Value2);
                    item.material_total = item.material_price * item.number;
                    item.bury_price = Convert.ToDouble(r.get_Offset(0, 3).Value2);
                    item.bury_total = item.bury_price * item.number;
                }
                if (r.get_Offset(0, 5).Value2 != null && content.parameters==0)
                {
                    content.parameters = (double)(r.get_Offset(0, 5).Value2);
                }
                content.items.Add(item);
                r = r.get_Offset(1, 0);
            }
            prj.contents.Add(content);

            //****************核定工作量
            r = sheet.Cells.Find("预估次数", miss, miss, miss, miss,
               XlSearchDirection.xlNext, miss, miss, miss);
            r = r.get_Offset(1, 0);
            while (r.Value2 != null && r.Value2.ToString() != string.Empty)
            {
                work_item item = new work_item();
                item.content = r.get_Offset(0, -5).Value2.ToString();
                item.number = Convert.ToDouble(r.get_Offset(0, -3).Value2);

                if (item.number < 1)
                {
                    //item.number = 0;
                    //prj.works.Add(item);
                    r = r.get_Offset(1, 0);
                    continue;
                }
                double ratio = 0;
                if (check == true)
                {
                    item.price = Convert.ToDouble(r.get_Offset(0, -2).Value2);
                    item.predict = Convert.ToDouble(r.Value2);
                }
                else
                {
                    item.price = get_work_price(item.content, ref ratio, latest3);
                    item.predict = prj.survey_no * ratio;
                }
                item.price_total = item.price * item.number;

                item.sum = item.predict * item.price_total;
                prj.works.Add(item);
                r = r.get_Offset(1, 0);
            }
            show_prj_detail(prj);
            show_prj_material(prj); load_graph();//加载数值和图像
        }
        //显示第一页
        void show_prj_detail(project prj)
        {
            txtprjid.Text = prj.prjid.ToString();
            txtprjcontract.Text = prj.contract.ToString();
            txtprjid.Text = prj.prjid;
            txtprjname.Text = prj.prjname;
            txtprjadress.Text = prj.address;
            txtprjmanager.Text = prj.manager;
            txtteamlead.Text = prj.leader;
            txtmanufact.Text = prj.manufacture;
            txtprjtype.Text = prj.type;
            txtno.Text = prj.survey_no.ToString();
            txtworkratio.Text = prj.work_ratio.ToString();
            txtprjratio.Text = prj.prj_ratio.ToString();
            txtbuyratio.Text = prj.manu_ratio.ToString();
        }
        //显示第二三页
        void show_prj_material(project prj)
        {
            prj.material_sum = 0;
            prj.bury_sum = 0;
            prj.work_sum = 0;
            grid.Rows.Clear();
            for (int i = 0; i < prj.contents.Count; i++)
            {
                for (int j = 0; j < prj.contents[i].items.Count; j++)
                {
                    object[] para ={prj.contents[i].name,prj.contents[i].items[j].method,prj.contents[i].items[j].number,prj.contents[i].items[j].material_price,
                                  prj.contents[i].items[j].material_total,prj.contents[i].items[j].bury_price,prj.contents[i].items[j].bury_total};
                    grid.Rows.Add(para);
                    prj.material_sum += prj.contents[i].items[j].material_total;
                    prj.bury_sum += prj.contents[i].items[j].bury_total;
                }
            }
            prj.material_sum = prj.material_sum * prj.manu_ratio;
            prj.material_bury = prj.material_sum + prj.bury_sum;
            
            //添加小计
            tree1.Nodes.Clear();
            Node sumnode = new Node();
            ElementStyle style = new ElementStyle();
            style.BackColor = Color.PaleVioletRed;
            style.BackColor2 = Color.White;
            sumnode.Style = style;
            sumnode.Text = "合计";
            sumnode.Cells.Add(new Cell((prj.material_bury).ToString()));
            tree1.Nodes.Add(sumnode);

            sumnode = new Node();
            sumnode.Text = "材料费";
            sumnode.Cells.Add(new Cell(prj.material_sum.ToString()));
            tree1.Nodes.Add(sumnode);


            sumnode = new Node();
            sumnode.Text = "埋设费";
            sumnode.Cells.Add(new Cell(prj.bury_sum.ToString()));
            tree1.Nodes.Add(sumnode);

            workgrid.Rows.Clear();
            tree2.Nodes.Clear();
            for (int i = 0; i < prj.works.Count; i++)
            {
                //if (i == 15)
                //{
                //    prj.works[i].number = prj.contents[i].getsum() + prj.contents[i + 1].getsum();
                //}
                //else if (i > 15)
                //{
                //    prj.works[i].number = prj.contents[i + 1].getsum();
                //}
                //else
                //{
                //    prj.works[i].number = prj.contents[i].getsum();
                //}
                if (prj.works[i].number == 0)
                {
                    continue;
                }
                prj.works[i].price_total = prj.works[i].number * prj.works[i].price;
                prj.works[i].sum = prj.works[i].price_total * prj.works[i].predict;
                object[] para ={prj.works[i].content, prj.works[i].number,
                                   prj.works[i].price,prj.works[i].price_total,
                    prj.works[i].predict,prj.works[i].sum};
                workgrid.Rows.Add(para);
                prj.work_sum += prj.works[i].sum;
            }

            sumnode = new Node();
            sumnode.Text = "核定工作量";
            sumnode.Cells.Add(new Cell(prj.work_sum.ToString()));
            tree2.Nodes.Add(sumnode);


            sumnode = new Node();
            sumnode.Text = "加入工程系数后工作量";
            prj.work_sum = prj.work_sum * prj.prj_ratio;
            sumnode.Cells.Add(new Cell(prj.work_sum.ToString()));
            sumnode.Style = style;
            tree2.Nodes.Insert(0, sumnode);

  
        }
        #region 保存
        //保存到数据库
        private void btn_save_Click(object sender, EventArgs e)
        {
            Thread thr = new Thread(new ThreadStart(save));
            thr.Start();
        }

        //保存函数
        void save()
        {
            mfrm.progressBar.Visible = true;
            mfrm.progressBar.ProgressType = eProgressItemType.Marquee;

            message_frm frm;
            if (prj == null)
            {
                frm = new message_frm("没有核算信息", "错误", icon_img.error);
                frm.ShowDialog(this);
                frm.Close();
                return;
            }
            frm = new message_frm("是否将核定信息存入数据库?", "询问", icon_img.query);
            frm.ShowDialog(this);
            if (frm.yesorno == false)
            {
                frm.Close();
                return;
            }
            else
            {
                frm.Close();
            }

            //开始有入库的代码
            string sql;
            sql = "select * from [projects] where [项目编号]='" + prj.prjid + "'";
            System.Data.DataTable dt = mfrm.m_db.GetDataSet(sql).Tables[0];
            if (dt == null || dt.Rows.Count == 0)
            {

            }
            else
            {
                sql = "select [材料埋设],[工作量] from [projects] where [项目编号]='" + prj.prjid + "'";
                dt = mfrm.m_db.GetDataSet(sql).Tables[0];
                double m = Convert.ToDouble(dt.Rows[0]["材料埋设"]);
                double w = Convert.ToDouble(dt.Rows[0]["工作量"]);
                bool bm = false;
                bool bw = false;
                if (m * 1.1 < prj.material_bury || m * 0.9 > prj.material_bury)
                {
                    bm = true;
                }
                if (w * 1.2 < prj.work_sum || w * 0.8 > prj.work_sum)
                {
                    bw = true;
                }
                if (bm == false && bw == false && savechk.Checked==false)
                {
                    frm =new message_frm("材料埋设和工作量均未超限,\r\n不需重算!","提示",icon_img.info);
                    frm.ShowDialog(this);
                    frm.Close();
                    return;
                }
                else if (bm == true && bw == true)
                {
                    frm = new message_frm("材料埋设和工作量均超限,\r\n是否重算!", "提示", icon_img.query);
                    frm.ShowDialog(this);
                    if (frm.yesorno == true)
                    {

                    }
                    else
                    {
                        frm.Close();
                        return;
                    }
                    frm.Close();
                }
                else if (bm == true)
                {
                    frm = new message_frm("材料埋设费用超限,\r\n是否重算!", "提示", icon_img.query);
                    frm.ShowDialog(this);
                    if (frm.yesorno == true)
                    {

                    }
                    else
                    {
                        frm.Close();
                        return;
                    }
                    frm.Close();
                }
                else if (bw == true)
                {
                    frm = new message_frm("核定工作量超限,\r\n是否重算!", "提示", icon_img.query);
                    frm.ShowDialog(this);
                    if (frm.yesorno == true)
                    {
                
                    }
                    else
                    {
                        frm.Close();
                        return;
                    }
                    frm.Close();
                }
            }
            sql = "delete from [projects] where [项目编号]='" + prj.prjid + "'";
            mfrm.m_db.ExecuteSQL(sql);
            sql = "delete from [projects_added] where [项目编号]='" + prj.prjid + "'";
            mfrm.m_db.ExecuteSQL(sql);
            sql = "Insert into [projects] values('" + prj.prjid.Trim() + "','" + prj.prjname.Trim() + "','" + prj.manager + "','"
                             + prj.manager + "'," + prj.contract + ",'" + prj.address + "','" + prj.type + "','" + prj.manufacture
                             + "','" + prj.leader + "'," + prj.survey_no + "," + prj.work_ratio + "," + prj.prj_ratio + "," +
                             prj.manu_ratio + ",'" + prj.date + "'," + prj.material_bury + "," + prj.work_sum + ",'在建','','','')";
            mfrm.m_db.ExecuteSQL(sql);
            into_base_details();
            mfrm.progressBar.Visible = false;
            frm = new message_frm("入库成功!", "提示", icon_img.info);
            frm.ShowDialog(this);
            frm.Close();
          
        }

        //把材料,埋设以及工作量细则存入数据库
        void into_base_details()
        {
            string sql;
            sql = "delete from [material] where [项目编号]='" + prj.prjid + "'";
            mfrm.m_db.ExecuteSQL(sql);
            sql = "delete from [workload] where [项目编号]='" + prj.prjid + "'";
            mfrm.m_db.ExecuteSQL(sql);
            sql = "delete from [projects_added] where [项目编号]='" + prj.prjid + "'";
            mfrm.m_db.ExecuteSQL(sql);

            for (int i = 0; i < prj.contents.Count; i++)
            {
                for (int j = 0; j < prj.contents[i].items.Count; j++)
                {
                    sql = "insert into [material] values('" + prj.prjid + "','" + prj.contents[i].name + "','"
                        + prj.contents[i].items[j].method
                        + "'," + prj.contents[i].items[j].number + "," + prj.contents[i].items[j].material_price + ","
                        + prj.contents[i].items[j].material_total + "," + prj.contents[i].items[j].bury_price + ","
                        + prj.contents[i].items[j].bury_total + "," + prj.contents[i].parameters + ",'','','','')"
                        ;
                    mfrm.m_db.ExecuteSQL(sql);
                }
            }

            for (int i = 0; i < prj.works.Count; i++)
            {
                if (prj.works[i].number == 0)
                {
                    continue;
                }
                sql = "insert into [workload] values('" + prj.prjid + "','" + prj.works[i].content + "',"
                    + prj.works[i].number + "," + prj.works[i].price + "," + prj.works[i].predict + ","
                    + prj.works[i].sum + ",'','','','')";
                mfrm.m_db.ExecuteSQL(sql);

                //sql = "insert into [workload_para] values('" + prj.works[i].content + "'," + prj.works[i].price
                //   + "," + prj.works[i].predict / prj.survey_no + ",'2000-1-1')";
                //mfrm.m_db.ExecuteSQL(sql);
            }

            sql = "insert into [projects_added] values('" + prj.prjid + "'," + prj.support_no + ","
                + prj.envi_complicate
                + "," + prj.operate_method + "," + prj.region_ratio + "," + prj.complicate_ratio + ","
                + prj.survey_type_ratio + "," + prj.team_ratio + "," + prj.scale_ratio + ")";
            mfrm.m_db.ExecuteSQL(sql);        
        }
        #endregion

        //导出到excel
        private void btnexport_Click(object sender, EventArgs e)
        {
            message_frm frm;
            if (mfrm.curprjid == null || mfrm.curprjid == "")
            {
                frm = new message_frm("工程编号不能为空", "错误", icon_img.alert1);
                frm.ShowDialog(this);
                frm.Close();
                return;
            }
            string path = System.Windows.Forms.Application.StartupPath + @"\报表\temp1.xls";
            //string xlsname = time1.Month + "月到" + time2.Month + "月工作量考核统计";
            string path1 = System.Windows.Forms.Application.StartupPath + @"\报表\" + mfrm.curprjid + ".xls";

            if (File.Exists(path1))
            {
                try
                {
                    File.Delete(path1);
                }
                catch (Exception a)
                {
                    MessageBox.Show(a.Message);
                    return;
                }
            }
            File.Copy(path, path1);
            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Excel.Workbook book = (Excel.Workbook)excel.Workbooks.Open(path1.ToString(),
                miss, miss, miss, miss, miss, miss,
                miss, miss, miss, miss, miss, miss, miss, miss);
            excel.Visible = true;
            Worksheet sheet = book.Sheets[1] as Worksheet;
            System.Data.DataTable dt; System.Data.DataTable ratiodt;
            string sql = "select * from [projects] where [项目编号]='" + mfrm.curprjid + "'";
            dt = m_db.GetDataSet(sql).Tables[0];
            sql = "select * from [projects_added] where [项目编号]='" + mfrm.curprjid + "'";
            ratiodt = m_db.GetDataSet(sql).Tables[0];

            Range r;
            r = sheet.Cells.Find("测算日期", miss, miss, miss, miss,
                           XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["测算日期"];
            r = sheet.Cells.Find("工程名称", miss, miss, miss, miss,
                         XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["项目名称"];
            r = sheet.Cells.Find("工程编号", miss, miss, miss, miss,
                     XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["项目编号"];

            r = sheet.Cells.Find("合同额", miss, miss, miss, miss,
         XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["合同额"];

            r = sheet.Cells.Find("地址", miss, miss, miss, miss,
         XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["地址"];

            r = sheet.Cells.Find("项目经理", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["项目经理"];

            r = sheet.Cells.Find("班组长", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["班组长"];

            r = sheet.Cells.Find("采购厂商", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["采购厂商"];

            r = sheet.Cells.Find("监测类型", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["监测类型"];

            r = sheet.Cells.Find("次数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["次数"];


            r = sheet.Cells.Find("基坑工作量系数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["工作量系数"];

            r = sheet.Cells.Find("工程综合系数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["综合系数"];

            r = sheet.Cells.Find("材料采购系数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(0, 1).Value2 = dt.Rows[0]["采购系数"];

            r = sheet.Cells.Find("支撑层数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["支撑层数"];

            r = sheet.Cells.Find("环境复杂性", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["环境复杂性"];

            r = sheet.Cells.Find("施作方式", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["施作方式"];

            r = sheet.Cells.Find("地域系数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["地域系数"];

            r = sheet.Cells.Find("复杂度系数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["复杂系数"];

            r = sheet.Cells.Find("监测类型系数", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["监测类型系数"];

            r = sheet.Cells.Find("班组折减率", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["班组折减率"];

            r = sheet.Cells.Find("规模效应折减", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            r.get_Offset(1, 0).Value2 = ratiodt.Rows[0]["规模效应折减"];


            //材料埋设费用
            r = sheet.Cells.Find("规格、方法", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            Range range = r;
            r = r.get_Offset(1, 0);
            sql = "select * from [material] where [项目编号]='" + mfrm.curprjid + "' order by [分项内容]";
            ratiodt = m_db.GetDataSet(sql).Tables[0];
            string item = ratiodt.Rows[0]["分项内容"].ToString();
            int index = 1;
            Range tempr = r.get_Offset(0, -2); Range m;
            for (int i = 0; i < ratiodt.Rows.Count; i++)
            {
                string method = ratiodt.Rows[i]["规格方法"].ToString();
                string name = ratiodt.Rows[i]["分项内容"].ToString();
                r.Value2 = method;
                if (i == 0)
                {
                    r.get_Offset(0, -1).Value2 = name;
                    r.get_Offset(0, -2).Value2 = index;
                }
                r.get_Offset(0, 1).Value2 = ratiodt.Rows[i]["数量"];
                r.get_Offset(0, 2).Value2 = ratiodt.Rows[i]["材料单价"];
                r.get_Offset(0, 3).Value2 = ratiodt.Rows[i]["材料小计"];
                r.get_Offset(0, 4).Value2 = ratiodt.Rows[i]["数量"];
                r.get_Offset(0, 5).Value2 = ratiodt.Rows[i]["埋设单价"];
                r.get_Offset(0, 6).Value2 = ratiodt.Rows[i]["埋设小计"];
                double para = (double)ratiodt.Rows[i]["参数"];
                if (para != 0)
                {
                    r.get_Offset(0, 7).Value2 = para;
                }
                if (item != name)
                {
                    item = name;
                    m = sheet.get_Range(tempr, r.get_Offset(-1, -2));
                    m.Merge(miss);
                    m = sheet.get_Range(tempr.get_Offset(0, 1), r.get_Offset(-1, -1));
                    m.Merge(miss);
                    index++;
                    tempr = r.get_Offset(0, -2);
                    r.get_Offset(0, -2).Value2 = index;
                    r.get_Offset(0, -1).Value2 = name;
                }
                r = r.get_Offset(1, 0);
            }
            m = sheet.get_Range(tempr, r.get_Offset(-1, -2));
            m.Merge(miss);
            m = sheet.get_Range(tempr.get_Offset(0, 1), r.get_Offset(-1, -1));
            m.Merge(miss);
            m = sheet.get_Range(r.get_Offset(0, -2), r);
            m.Merge(miss);
            m.Value2 = "材料费与埋设费合计";
            r.get_Offset(0, 7).Value2 = dt.Rows[0]["材料埋设"];
            r.get_Offset(0, 7).Font.Bold = true;



            Range r1 = sheet.get_Range(range.get_Offset(1, -2), r.get_Offset(0, 7));
            r1.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

            range = sheet.Cells.Find("核定工作量测算表", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            index = range.Row - r.Row;
            int row = r.Row + 1;
            while (index > 4)
            {
                (sheet.Rows[row, miss] as Range).Delete(miss);
                index--;
            }
            range = range.get_Offset(2, 0); r = range;
            sql = "select * from [workload] where [项目编号]='" + mfrm.curprjid + "'";
            ratiodt = m_db.GetDataSet(sql).Tables[0];
            for (int i = 0; i < ratiodt.Rows.Count; i++)
            {
                range.Value2 = i + 1;
                m = sheet.get_Range(range.get_Offset(0, 1), range.get_Offset(0, 2));
                m.Merge(miss);
                m.Value2 = ratiodt.Rows[i]["分项内容"];
                range.get_Offset(0, 3).Value2 = ratiodt.Rows[i]["数量"];
                range.get_Offset(0, 4).Value2 = ratiodt.Rows[i]["单价"];
                range.get_Offset(0, 5).Value2 = (double)range.get_Offset(0, 3).Value2 *
                    (double)range.get_Offset(0, 4).Value2;
                range.get_Offset(0, 6).Value2 = ratiodt.Rows[i]["预估次数"];
                range.get_Offset(0, 7).Value2 = ratiodt.Rows[i]["观测费用"];
                range = range.get_Offset(1, 0);
            }
            r1 = sheet.get_Range(r, range.get_Offset(0, 7));
            r1.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            range.Value2 = "合计";
            m = sheet.get_Range(range.get_Offset(0, 1), range.get_Offset(0, 7));
            m.Merge(miss);
            m.Value2 = dt.Rows[0]["工作量"];
            m.Font.Bold = true;

            r = sheet.Cells.Find("会签栏", miss, miss, miss, miss,
        XlSearchDirection.xlNext, miss, miss, miss);
            index = r.Row - range.Row;
            row = range.Row + 1;
            while (index > 4)
            {
                (sheet.Rows[row, miss] as Range).Delete(miss);
                index--;
            }

        }

        #region 获取参数
        double get_material_price(string content, string item, string latest)
        {
            string sql = "select [材料单价] from [material_para] where [分项内容]='" + content
                + "' and [规格方法]='" + item + "' and [日期]='" + latest + "'";
            double val = 0;
            try
            {
                val = (double)mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][0];
            }
            catch
            {
                val = 0;
            }
            return val;
        }

        double get_bury_price(string content, string item, string latest)
        {
            string sql = "select [埋设单价] from [bury_para] where [分项内容]='" + content
                + "' and [规格方法]='" + item + "' and [日期]='" + latest + "'";
            double val = 0;
            try
            {
                val = (double)mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][0];
            }
            catch
            {
                val = 0;
            }
            return val;
        }
       
        double get_work_price(string content, ref double ratio, string latest)
        {
            string sql = "select [单价],[比例] from [workload_para] where [分项内容]='" + content
                + "' and [日期]='" + latest + "'";
            double val = 0;
            try
            {
                val = (double)mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][0];
                ratio = (double)mfrm.m_db.GetDataSet(sql).Tables[0].Rows[0][1];
            }
            catch
            {
                val = 0;
                ratio = 0;
            }
            return val;
        }
        #endregion

        private void btnoutput_Click(object sender, EventArgs e)
        {
            input_excel_frm frm = new input_excel_frm(this);
            frm.ShowDialog(this);
        }

        #region 编辑grid
        object gridv;
        int x, y;
        private void grid_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            int column = e.ColumnIndex;
            message_frm frm;
            if (column == 0 || column > 4 || column == 2)
            {
                frm = new message_frm("无法更改,请从Excel里改写", "错误", icon_img.error);
                frm.ShowDialog(this);
                frm.Close();
                e.Cancel = true;
                return;
            }
            if (column >= 2)
            {
                gridv = grid.Rows[e.RowIndex].Cells[column].Value;
            }
            x= prj.contents.FindIndex(new Predicate<item_content>(Findx));
            y = prj.contents[x].items.FindIndex(new Predicate<material_item>(Findy)); 
        }
        bool Findx(item_content item)
        {

            int row = grid.SelectedCells[0].RowIndex;
            string content = grid.Rows[row].Cells[0].Value.ToString();
            if (item.name == content)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        bool Findy(material_item item)
        {
            int row = grid.SelectedCells[0].RowIndex;
            string content = grid.Rows[row].Cells[1].Value.ToString();
            if (item.method == content)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        private void grid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            int column = e.ColumnIndex;
            message_frm frm;
            string txt = grid.Rows[e.RowIndex].Cells[column].Value.ToString();
            double d = 0;
            if (column >= 2)
            {
                try
                {
                    d = Convert.ToDouble(txt);
                }
                catch
                {
                    frm = new message_frm("输入格式有误,请重新输入", "错误", icon_img.error);
                    frm.ShowDialog(this);
                    frm.Close();
                    grid.Rows[e.RowIndex].Cells[column].Value = gridv;
                    return;
                }
            }
            switch (column)
            {
                case 1:
                    {
                        prj.contents[x].items[y].method = txt;
                        break;
                    }
                case 2:
                    {
                        prj.contents[x].items[y].number = (int)d;
                        prj.contents[x].items[y].material_total = prj.contents[x].items[y].material_price 
                            * d;
                        prj.contents[x].items[y].bury_total = prj.contents[x].items[y].bury_price * d;
                        show_prj_material(prj);
                        break;
                    }
                case 3:
                    {
                        prj.contents[x].items[y].material_price = d;
                        prj.contents[x].items[y].material_total = d * prj.contents[x].items[y].number;
                        show_prj_material(prj);
                        break;
                    }
                case 4:
                    {
                        prj.contents[x].items[y].bury_price = d;
                        prj.contents[x].items[y].bury_total = d * prj.contents[x].items[y].number;
                        show_prj_material(prj);
                        break;
                    }
            }


        }

        private void workgrid_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            int column = e.ColumnIndex;
            message_frm frm;
            if (column != 2)
            {
                frm = new message_frm("只能更改单价,其他内容请从Excel里改写", "错误", icon_img.error);
                frm.ShowDialog(this);
                frm.Close();
                e.Cancel = true;
                return;
            }
            gridv = workgrid.Rows[e.RowIndex].Cells[column].Value;
        }

        private void workgrid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            int column = e.ColumnIndex;
            message_frm frm;
            string txt = workgrid.Rows[e.RowIndex].Cells[column].Value.ToString();
            double d = 0;
            try
            {
                d = Convert.ToDouble(txt);
            }
            catch
            {
                frm = new message_frm("输入格式有误,请重新输入", "错误", icon_img.error);
                frm.ShowDialog(this);
                frm.Close();
                workgrid.Rows[e.RowIndex].Cells[column].Value = gridv;
                return;
            }
            int index = prj.works.FindIndex(new Predicate<work_item>(Findz));
            prj.works[index].price = d;

            show_prj_material(prj);
        }

        bool Findz(work_item item)
        {
            int row = workgrid.SelectedCells[0].RowIndex;
            string content = workgrid.Rows[row].Cells[0].Value.ToString();
            if (item.content == content)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 选择工程
        private void btn_chooseprj_Click(object sender, EventArgs e)
        {


        }

        void load_data()
        {
            message_frm frm;
            if (mfrm.curprjid == null || mfrm.curprjid == "")
            {
                frm = new message_frm("工程编号不能为空", "错误", icon_img.alert1);
                frm.ShowDialog(this);
                frm.Close();
                return;
            }
            prj = new project();
            string sql = "select * from [projects] where [项目编号]='" + mfrm.curprjid + "'";
            System.Data.DataTable dt = m_db.GetDataSet(sql).Tables[0];
            sql = "select * from [projects_added] where [项目编号]='" + mfrm.curprjid + "'";
            System.Data.DataTable ratiodt = m_db.GetDataSet(sql).Tables[0];





            prj.date = Convert.ToDateTime(dt.Rows[0]["测算日期"]);
            prj.prjname = dt.Rows[0]["项目名称"].ToString();
            prj.prjid = dt.Rows[0]["项目编号"].ToString();
            prj.contract=Convert.ToDouble(dt.Rows[0]["合同额"]);
            prj.address = dt.Rows[0]["地址"].ToString();
            prj.manager = dt.Rows[0]["项目经理"].ToString();
            prj.leader = dt.Rows[0]["班组长"].ToString();
            prj.manufacture = dt.Rows[0]["采购厂商"].ToString();
            prj.type=dt.Rows[0]["监测类型"].ToString();
            prj.survey_no=Convert.ToDouble(dt.Rows[0]["次数"]);
            prj.work_ratio = Convert.ToDouble(dt.Rows[0]["工作量系数"]);
            prj.prj_ratio = Convert.ToDouble(dt.Rows[0]["综合系数"]);
            prj.manu_ratio = Convert.ToDouble(dt.Rows[0]["采购系数"]);
            prj.support_no = Convert.ToDouble(ratiodt.Rows[0]["支撑层数"]);
            prj.envi_complicate = Convert.ToDouble(ratiodt.Rows[0]["环境复杂性"]);
            prj.operate_method = Convert.ToDouble(ratiodt.Rows[0]["施作方式"]);
            prj.region_ratio = Convert.ToDouble(ratiodt.Rows[0]["地域系数"]);
            prj.complicate_ratio = Convert.ToDouble(ratiodt.Rows[0]["复杂系数"]);
            prj.survey_type_ratio = Convert.ToDouble(ratiodt.Rows[0]["监测类型系数"]);
            prj.team_ratio = Convert.ToDouble(ratiodt.Rows[0]["班组折减率"]);
            prj.scale_ratio = Convert.ToDouble(ratiodt.Rows[0]["规模效应折减"]);

            sql = "select * from [material] where [项目编号]='" + mfrm.curprjid + "' order by [分项内容]";
            ratiodt = m_db.GetDataSet(sql).Tables[0];
            string item = ratiodt.Rows[0]["分项内容"].ToString();
            item_content content = new item_content();
            for (int i = 0; i < ratiodt.Rows.Count; i++)
            {
                string method = ratiodt.Rows[i]["规格方法"].ToString();
                string name = ratiodt.Rows[i]["分项内容"].ToString();
                if (item == name)
                {
                    content.name = name;
                    content.parameters = (double)ratiodt.Rows[i]["参数"];
                }
                else
                {
                    prj.contents.Add(content);
                    content = new item_content();
                    content.name = name;
                    item = name;
                }
                material_item m_item = new material_item();
                m_item.method = method;
                m_item.number = Convert.ToInt32(ratiodt.Rows[i]["数量"]);
                m_item.material_price=Convert.ToDouble(ratiodt.Rows[i]["材料单价"]);
                m_item.material_total = Convert.ToDouble(ratiodt.Rows[i]["材料小计"]);
                m_item.bury_price = Convert.ToDouble(ratiodt.Rows[i]["埋设单价"]);
                m_item.bury_total = Convert.ToDouble(ratiodt.Rows[i]["埋设小计"]);
                content.items.Add(m_item);
            }

            sql = "select * from [workload] where [项目编号]='" + mfrm.curprjid + "'";
            ratiodt = m_db.GetDataSet(sql).Tables[0];
            for (int i = 0; i < ratiodt.Rows.Count; i++)
            {
                work_item witem = new work_item();
                witem.content = ratiodt.Rows[i]["分项内容"].ToString();
                witem.number=Convert.ToDouble(ratiodt.Rows[i]["数量"]);
                witem.price = Convert.ToDouble(ratiodt.Rows[i]["单价"]);
                witem.price_total = witem.number * witem.price;
                witem.predict = Convert.ToDouble(ratiodt.Rows[i]["预估次数"]);
                witem.sum = Convert.ToDouble(ratiodt.Rows[i]["观测费用"]);
                prj.works.Add(witem);
            }
            show_prj_detail(prj);
            show_prj_material(prj);
            load_graph();


        }

        void load_graph()
        {
          
            GraphPane myPane = zed.GraphPane;
            myPane.CurveList.Clear();
            
            //myPane = new GraphPane();
            myPane.Title.Text = prj.prjname;
            myPane.Title.FontSpec.IsItalic = true;
            myPane.Title.FontSpec.Size = 12f;
            myPane.Title.FontSpec.Family = "Times New Roman";


            
            myPane.Fill = new Fill(Color.White, Color.LightBlue, 45.0f);
            // No fill for the axis background
            myPane.Chart.Fill.Type = FillType.None;

            // Set the legend to an arbitrary location
            myPane.Legend.Position = LegendPos.Float;
            myPane.Legend.Location = new Location(0.95f, 0.15f, CoordType.PaneFraction,
                                AlignH.Right, AlignV.Top);
            myPane.Legend.FontSpec.Size = 10f;
            myPane.Legend.IsHStack = false;

            // Add some pie slices
            double ratio = 0;
            ratio = Math.Round((prj.material_bury / prj.contract) * 100, 1) ;
            PieItem segment1 = myPane.AddPieSlice(prj.material_bury, Color.BlanchedAlmond, Color.White, 45f, 0.2, "材料埋设费用 " + ratio.ToString() + "%");
            ratio = Math.Round((prj.work_sum / prj.contract) * 100, 1);
            PieItem segment3 = myPane.AddPieSlice(prj.work_sum, Color.DarkSlateBlue, Color.White, 45f, .0, "考核工作量 " + ratio.ToString() + "%");
            ratio = Math.Round(((prj.contract - prj.work_sum - prj.material_bury) / prj.contract) * 100, 1);
            PieItem segment4 = myPane.AddPieSlice(prj.contract - prj.work_sum - prj.material_bury, 
                Color.HotPink, Color.White, 45f, 0, "其他 " + ratio.ToString() + "%");
            
            //PieItem segment2 = myPane.AddPieSlice(40, Color.SandyBrown, Color.White, 45f, 0.2, "South");
            //PieItem segment6 = myPane.AddPieSlice(250, Color.Red, Color.White, 45f, 0, "Europe");
            //PieItem segment7 = myPane.AddPieSlice(50, Color.Blue, Color.White, 45f, 0.2, "Pac Rim");
            //PieItem segment8 = myPane.AddPieSlice(400, Color.Green, Color.White, 45f, 0, "South America");
            //PieItem segment9 = myPane.AddPieSlice(50, Color.Yellow, Color.White, 45f, 0.2, "Africa");
            TextObj text = new TextObj("总合同额\n" + "￥" + Math.Floor(prj.contract),
                0.18F, 0.40F, CoordType.PaneFraction);
            text.FontSpec.Size = 22;
            text.Location.AlignH = AlignH.Center;
            text.Location.AlignV = AlignV.Bottom;
            text.FontSpec.Border.IsVisible = false;
            text.FontSpec.Fill = new Fill(Color.White, Color.Red, 45F);
            text.FontSpec.StringAlignment = StringAlignment.Center;
            myPane.GraphObjList.Add(text);
            zed.AxisChange();
            zed.Refresh();
        }
        #endregion



    }
}
